package com.anolesoft.amljmes.fourteenDispatch.service.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.amljmes.fourteenDispatch.controller.TaskViewController;
import com.anolesoft.amljmes.fourteenDispatch.dto.MesPlanDispatchingDto;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface TaskViewServiceMapper {
    // 查询派工人列表
    @SelectProvider(type=createSQL.class, method = "querySendWorkersSQL")
    List<MesPlanDispatchingDto> querySendWorkersSQL(TaskViewController.DemoVO demoVO);
    public static class createSQL {
        public static String querySendWorkersSQL(TaskViewController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append("a.objuid as key,\n" +
                            "       a.objuid,\n" +
                            "       a.pacr_no,\n" +
                            "       a.jixing,\n" +
                            "       a.tuhao,\n" +
                            "       a.paigonghao,\n" +
                            "       a.gongxuid,\n" +
                            "       a.gongxuname,\n" +
                            "       a.paigongtime,\n" +
                            "       a.checktime,\n" +
                            "       case\n" +
                            "         when b.processstate = 70 and b.actendtime > a.checktime then\n" +
                            "          1\n" +
                            "         when b.processstate = 70 and b.actendtime <= (a.checktime+1) then\n" +
                            "          2\n" +
                            "         when b.processstate != 70 and sysdate <= (a.checktime+1) then\n" +
                            "          3\n" +
                            "         else\n" +
                            "          4\n" +
                            "       end as processstate,\n" +
                            "       c.name paigongren,\n" +
                            "       d.name caozuozhe,\n" +
                            "       0 as pageNo,\n" +
                            "       10 as pageSize\n" +
                            "  from mes_plan_dispatch a\n" +
                            "  left join mes_process_trace b\n" +
                            "    on a.process_trace_obj = b.objuid\n" +
                            "  left join stf_employee c\n" +
                            "    on a.paigongren = c.objuid\n" +
                            "  left join stf_employee d\n" +
                            "    on a.caozuozhe = d.objuid\n" +
                            " where a.state = 0 ");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPacrNo())){
                        String pacrNo = demoVO.getPacrNo();
                        sql.append(" and upper(a.pacr_no) = trim(upper(#{pacrNo, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getJixing())){
                        String jixing = demoVO.getJixing();
                        sql.append(" and upper(a.jixing) = trim(upper(#{jixing, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getTuhao())){
                        String tuhao = demoVO.getTuhao();
                        sql.append(" and upper(a.tuhao) = trim(upper(#{tuhao, jdbcType=VARCHAR}))");
                    }
                    if(demoVO.getProcessstate()!= null){
                        int processstate=demoVO.getProcessstate();
                        if (processstate==1) {
                            sql.append(" and b.processstate = 70 and b.actendtime > a.checktime");
                        } else if (processstate==2){
                            sql.append(" and b.processstate = 70 and b.actendtime <= (a.checktime+1)");
                        } else if (processstate==3){
                            sql.append(" and b.processstate != 70 and sysdate <= (a.checktime+1)");
                        } else {
                            sql.append(" and b.processstate != 70 and sysdate > a.checktime");
                        }
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPaigongren())){
                        String paigongren = demoVO.getPaigongren();
                        sql.append(" and upper(c.name) = trim(upper(#{paigongren, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getCaozuozhe())){
                        String caozuozhe = demoVO.getCaozuozhe();
                        sql.append(" and upper(d.name) = trim(upper(#{caozuozhe, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactoryId())){
                        String factoryId = demoVO.getFactoryId();
                        sql.append(" and upper(a.factory_id) = upper(#{factoryId, jdbcType=VARCHAR})");
                    }
                    sql.append("order by a.paigongtime desc");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 查询操作者列表
    @SelectProvider(type=createSQL1.class, method = "queryHandlersSQL")
    List<MesPlanDispatchingDto> queryHandlersSQL(TaskViewController.DemoVO demoVO);
    public static class createSQL1 {
        public static String queryHandlersSQL(TaskViewController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append("a.objuid as key,\n" +
                            "       a.objuid,\n" +
                            "       a.pacr_no,\n" +
                            "       a.jixing,\n" +
                            "       a.tuhao,\n" +
                            "       a.paigonghao,\n" +
                            "       a.gongxuid,\n" +
                            "       a.gongxuname,\n" +
                            "       a.paigongtime,\n" +
                            "       a.checktime,\n" +
                            "       case\n" +
                            "         when b.processstate = 70 and b.actendtime > a.checktime then\n" +
                            "          1\n" +
                            "         when b.processstate = 70 and b.actendtime <= (a.checktime+1) then\n" +
                            "          2\n" +
                            "         when b.processstate != 70 and sysdate <= (a.checktime+1) then\n" +
                            "          3\n" +
                            "         else\n" +
                            "          4\n" +
                            "       end as processstate,\n" +
                            "       c.name paigongren,\n" +
                            "       d.name caozuozhe,\n" +
                            "       0 as pageNo,\n" +
                            "       10 as pageSize\n" +
                            "  from mes_plan_dispatch a\n" +
                            "  left join mes_process_trace b\n" +
                            "    on a.process_trace_obj = b.objuid\n" +
                            "  left join stf_employee c\n" +
                            "    on a.paigongren = c.objuid\n" +
                            "  left join stf_employee d\n" +
                            "    on a.caozuozhe = d.objuid\n" +
                            " where a.state = 0 " +
                            " and a.caozuozhe=#{caozuozhe, jdbcType=VARCHAR} ");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPacrNo())){
                        String pacrNo = demoVO.getPacrNo();
                        sql.append(" and upper(a.pacr_no) = trim(upper(#{pacrNo, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getJixing())){
                        String jixing = demoVO.getJixing();
                        sql.append(" and upper(a.jixing) = trim(upper(#{jixing, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getTuhao())){
                        String tuhao = demoVO.getTuhao();
                        sql.append(" and upper(a.tuhao) = trim(upper(#{tuhao, jdbcType=VARCHAR}))");
                    }
                    if(demoVO.getProcessstate()!= null){
                        int processstate=demoVO.getProcessstate();
                        if (processstate==1) {
                            sql.append(" and b.processstate = 70 and b.actendtime > a.checktime");
                        } else if (processstate==2){
                            sql.append(" and b.processstate = 70 and b.actendtime <= (a.checktime+1)");
                        } else if (processstate==3){
                            sql.append(" and b.processstate != 70 and sysdate <= (a.checktime+1)");
                        } else {
                            sql.append(" and b.processstate != 70 and sysdate > a.checktime");
                        }
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPaigongren())){
                        String paigongren = demoVO.getPaigongren();
                        sql.append(" and upper(c.name) = trim(upper(#{paigongren, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactoryId())){
                        String factoryId = demoVO.getFactoryId();
                        sql.append(" and upper(a.factory_id) = upper(#{factoryId, jdbcType=VARCHAR})");
                    }
                    sql.append("order by a.urgency");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 查询派工人列表
    @SelectProvider(type=createSQL3.class, method = "queryPgrForJiedanSQL")
    List<MesPlanDispatchingDto> queryPgrForJiedan(TaskViewController.DemoVO demoVO);
    public static class createSQL3 {
        public static String queryPgrForJiedanSQL(TaskViewController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append("a.objuid as key,\n" +
                            "       a.objuid,\n" +
                            "       a.pacr_no,\n" +
                            "       a.jixing,\n" +
                            "       a.tuhao,\n" +
                            "       a.paigonghao,\n" +
                            "       a.gongxuid,\n" +
                            "       a.gongxuname,\n" +
                            "       a.jiedantime,\n" +
                            "       a.checktime,\n" +
                            "       case\n" +
                            "         when b.processstate = 70 and b.actendtime > a.plantime then\n" +
                            "          1\n" +
                            "         when b.processstate = 70 and b.actendtime <= (a.plantime+1) then\n" +
                            "          2\n" +
                            "         when b.processstate != 70 and sysdate <= (a.plantime+1) then\n" +
                            "          3\n" +
                            "         else\n" +
                            "          4\n" +
                            "       end as processstate,\n" +
                            "       c.name jiedanren,\n" +
                            "       d.name caozuozhe,\n" +
                            "       0 as pageNo,\n" +
                            "       10 as pageSize\n" +
                            "  from mes_plan_dispatch a\n" +
                            "  left join mes_process_trace b\n" +
                            "    on a.process_trace_obj = b.objuid\n" +
                            "  left join stf_employee c\n" +
                            "    on a.jiedanren = c.objuid\n" +
                            "  left join stf_employee d\n" +
                            "    on a.caozuozhe = d.objuid\n" +
                            " where a.state = 0 ");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPacrNo())){
                        String pacrNo = demoVO.getPacrNo();
                        sql.append(" and upper(a.pacr_no) = trim(upper(#{pacrNo, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getJixing())){
                        String jixing = demoVO.getJixing();
                        sql.append(" and upper(a.jixing) = trim(upper(#{jixing, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getTuhao())){
                        String tuhao = demoVO.getTuhao();
                        sql.append(" and upper(a.tuhao) = trim(upper(#{tuhao, jdbcType=VARCHAR}))");
                    }
                    if(demoVO.getProcessstate()!= null){
                        int processstate=demoVO.getProcessstate();
                        if (processstate==1) {
                            sql.append(" and b.processstate = 70 and b.actendtime > a.plantime");
                        } else if (processstate==2){
                            sql.append(" and b.processstate = 70 and b.actendtime <= (a.plantime+1)");
                        } else if (processstate==3){
                            sql.append(" and b.processstate != 70 and sysdate <= (a.plantime+1)");
                        } else {
                            sql.append(" and b.processstate != 70 and sysdate > a.plantime");
                        }
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPaigongren())){
                        String paigongren = demoVO.getPaigongren();
                        sql.append(" and upper(c.name) = trim(upper(#{paigongren, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getCaozuozhe())){
                        String caozuozhe = demoVO.getCaozuozhe();
                        sql.append(" and upper(d.name) = trim(upper(#{caozuozhe, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactoryId())){
                        String factoryId = demoVO.getFactoryId();
                        sql.append(" and upper(a.factory_id) = upper(#{factoryId, jdbcType=VARCHAR})");
                    }
                    sql.append("order by a.paigongtime desc");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 查询操作者列表
    @SelectProvider(type=createSQL4.class, method = "queryCzzForJiedanSQL")
    List<MesPlanDispatchingDto> queryCzzForJiedan(TaskViewController.DemoVO demoVO);
    public static class createSQL4 {
        public static String queryCzzForJiedanSQL(TaskViewController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append("a.objuid as key,\n" +
                            "       a.objuid,\n" +
                            "       a.pacr_no,\n" +
                            "       a.jixing,\n" +
                            "       a.tuhao,\n" +
                            "       a.paigonghao,\n" +
                            "       a.gongxuid,\n" +
                            "       a.gongxuname,\n" +
                            "       a.jiedantime," +
                            "       a.planstarttime," +
                            "       a.planendtime,\n" +
                            "       a.checktime,\n" +
                            "       a.urgency,\n" +
                            "       nvl(e.qiliangtime,8)/8 as qiliang,\n" +
                            "       a.plantime,\n" +
                            "       case\n" +
                            "         when b.processstate = 70 and b.actendtime > a.plantime then\n" +
                            "          1\n" +
                            "         when b.processstate = 70 and b.actendtime <= (a.plantime+1) then\n" +
                            "          2\n" +
                            "         when b.processstate != 70 and sysdate <= (a.plantime+1) then\n" +
                            "          3\n" +
                            "         else\n" +
                            "          4\n" +
                            "       end as processstate,\n" +
                            "       c.name jiedanren,\n" +
                            "       d.name caozuozhe,\n" +
                            "       0 as pageNo,\n" +
                            "       10 as pageSize\n" +
                            "  from mes_plan_dispatch a\n" +
                            "  left join mes_process_trace b\n" +
                            "    on a.process_trace_obj = b.objuid\n" +
                            "  left join stf_employee c\n" +
                            "    on a.jiedanren = c.objuid\n" +
                            "  left join stf_employee d\n" +
                            "    on a.caozuozhe = d.objuid\n" +
                            "  left join mes_process_qlbz e \n" +
                            "    on a.tuhao = e.partid and a.gongxuid = e.gongxuid and a.gongxuname = e.gongxuname\n" +
                            " where a.state = 0 " +
                            " and a.caozuozhe=#{caozuozhe, jdbcType=VARCHAR} ");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPacrNo())){
                        String pacrNo = demoVO.getPacrNo();
                        sql.append(" and upper(a.pacr_no) = trim(upper(#{pacrNo, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getJixing())){
                        String jixing = demoVO.getJixing();
                        sql.append(" and upper(a.jixing) = trim(upper(#{jixing, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getTuhao())){
                        String tuhao = demoVO.getTuhao();
                        sql.append(" and upper(a.tuhao) = trim(upper(#{tuhao, jdbcType=VARCHAR}))");
                    }
                    if(demoVO.getProcessstate()!= null){
                        int processstate=demoVO.getProcessstate();
                        if (processstate==1) {
                            sql.append(" and b.processstate = 70 and b.actendtime > a.plantime");
                        } else if (processstate==2){
                            sql.append(" and b.processstate = 70 and b.actendtime <= (a.plantime+1)");
                        } else if (processstate==3){
                            sql.append(" and b.processstate != 70 and sysdate <= (a.plantime+1)");
                        } else {
                            sql.append(" and b.processstate != 70 and sysdate > a.plantime");
                        }
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getPaigongren())){
                        String paigongren = demoVO.getPaigongren();
                        sql.append(" and upper(c.name) = trim(upper(#{paigongren, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactoryId())){
                        String factoryId = demoVO.getFactoryId();
                        sql.append(" and upper(a.factory_id) = upper(#{factoryId, jdbcType=VARCHAR})");
                    }
                    sql.append("order by a.urgency");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }
}
